package dao;

import entity.Music;
import util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MusicDao {
    //插入
    public int insert(String title,String singer,String time,String url,int userid){
        Connection connection = null;
        PreparedStatement statement = null;
        connection = DBUtil.getConn();
        String sql = "insert into music(title,singer,time,url,userid) values(?,?,?,?,?)";
        try {
            statement = connection.prepareStatement(sql);
            statement.setString(1,title);
            statement.setString(2,singer);
            statement.setString(3,time);
            statement.setString(4,url);
            statement.setInt(5,userid);
            int ret = statement.executeUpdate();
            if (ret == 0){
                return 0;//插入失败
            }
            return ret;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.getClose(connection,statement,null);
        }
        return 0;
    }
    //查询全部歌单
    public List<Music> findMusic(){
        List<Music> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        connection = DBUtil.getConn();
        String sql = "select * from music";
        try {
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            while(resultSet.next()){
                Music music = new Music();
                music.setTitle(resultSet.getString("title"));
                music.setSinger(resultSet.getString("singer"));
                music.setTime(resultSet.getString("time"));
                music.setUrl(resultSet.getString("url"));
                music.setUserid(resultSet.getInt("userid"));
                list.add(music);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.getClose(connection,statement,resultSet);
        }
        return list;
    }
    //根据id查询音乐
    public Music findMusicById(int id){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Music music = null;
        connection = DBUtil.getConn();
        String sql = "select * from music where id = ?";
        try {
            statement = connection.prepareStatement(sql);
            statement.setInt(1,id);
            resultSet = statement.executeQuery();
            if (resultSet.next()){
                music = new Music();
                music.setTitle(resultSet.getString("title"));
                music.setSinger(resultSet.getString("singer"));
                music.setTime(resultSet.getString("time"));
                music.setUrl(resultSet.getString("url"));
                music.setUserid(resultSet.getInt("userid"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.getClose(connection,statement,resultSet);
        }
        return music;
    }
    //根据关键字查询歌单
    public List<Music> findMusicByKey(String str){
        List<Music> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        connection = DBUtil.getConn();
        String sql = "select * from music where title like ?";
        try {
            statement = connection.prepareStatement(sql);
            str = "%"+str+"%";
            statement.setString(1,str);
            resultSet = statement.executeQuery();
            while(resultSet.next()){
                Music music = new Music();
                music.setTitle(resultSet.getString("title"));
                music.setSinger(resultSet.getString("singer"));
                music.setTime(resultSet.getString("time"));
                music.setUrl(resultSet.getString("url"));
                music.setUserid(resultSet.getInt("userid"));
                list.add(music);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.getClose(connection,statement,resultSet);
        }
        return list;
    }
    //删除歌曲,同时删除我喜欢的列表数据库中进行删除
    public int deleteMusicById(int musicId){
        Connection connection = null;
        PreparedStatement statement = null;
        connection = DBUtil.getConn();
        String sql = "delete from music where id=?";
        try {
            statement = connection.prepareStatement(sql);
            statement.setInt(1,musicId);
            int ret = statement.executeUpdate();
            if(ret == 1){
                if(findLoveMusicById(musicId)){
                    int ret1 = deleteLoveMusicById(musicId);
                    if (ret1 == 1){
                        System.out.println("删除成功");
                        return 1;
                    }
                }
                return 1;
            }else {
                return 0;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.getClose(connection,statement,null);
        }
        return 0;
    }
    //删除第三张表中音乐数据
    public int deleteLoveMusicById(int musicId){
        Connection connection = null;
        PreparedStatement statement = null;
        connection = DBUtil.getConn();
        String sql = "delete from lovemiusic where music_id=?";
        try {
            statement = connection.prepareStatement(sql);
            statement.setInt(1,musicId);
            int ret = statement.executeUpdate();
            if (ret == 1){
                return 1;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.getClose(connection,statement,null);
        }
        return 0;
    }
    //看中间表是否有该id音乐数据
    public boolean findLoveMusicById(int musicId){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        connection = DBUtil.getConn();
        String sql = "select * from lovemiusic where music_id=?";
        try {
            statement = connection.prepareStatement(sql);
            statement.setInt(1,musicId);
            resultSet = statement.executeQuery();
            if (resultSet.next()){
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.getClose(connection,statement,resultSet);
        }
        return false;
    }
    public static void main4(String[] args) {
        MusicDao musicDao = new MusicDao();
        List<Music> list = musicDao.findMusicByKey("素");
        for (Music music:list){
            System.out.println(music);
        }
    }
    public static void main3(String[] args) {
        MusicDao musicDao = new MusicDao();
        Music music = musicDao.findMusicById(1);
            System.out.println(music);
    }
    public static void main2(String[] args) {
        MusicDao musicDao = new MusicDao();
        List<Music> list = musicDao.findMusic();
        for (Music music:list){
            System.out.println(music);
        }
    }
    public static void main1(String[] args) {
        MusicDao musicDao = new MusicDao();
        musicDao.insert("素颜","许嵩","2007-05-07","music\\素颜",2);
    }

}
